package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IStationEnShiftStatisService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Map;

@Service("StationEnShiftStatisService")
public class StationEnShiftStatisService implements IStationEnShiftStatisService {
    private Connection connPre = null;
    private static Map<String, String> stationMap = null;

    @Override
    public void syncRecord() throws Exception{
        final Integer m_statisDays = 6;

        final String recordName = "TB_SYNC_TRC_ENPU";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String sql = "update TB_STATIS_STATION_ENSHIFT set STATION_TOTALCNT=0 \n" +
                    "where SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') ";
            Integer count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]重置ETC出口工班表统计数据(条):" + count);

            int nBeforeDays = m_statisDays + 1;
            //1.统计ETC出口工班表数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_ENSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "  select x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE,sum(nvl(x.TOTAL_COUNT,0)) TOTAL_COUNT\n" +
                    "  from TB_SYNC_ENSHIFT x,(\n" +
                    "    select STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO,max(SEQ_ID) SEQ_ID \n" +
                    "    from TB_SYNC_ENSHIFT group by STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO) y \n" +
                    "  where x.SQUARD_TYPE='etc' and x.STATION_ID=y.STATION_ID and x.SQUAD_DATE=y.SQUAD_DATE and x.SQUAD_NO=y.SQUAD_NO and x.LANE_NO=y.LANE_NO \n" +
                    "  and x.SQUARD_TYPE=y.SQUARD_TYPE and nvl(x.OPERATORNO,-51)=nvl(y.OPERATORNO,-51) and x.SEQ_ID=y.SEQ_ID \n" +
                    "  and x.SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "  group by x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" + 
                    "  UPDATE SET a.STATION_TOTALCNT=b.TOTAL_COUNT,UPDATE_TIME=sysdate \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,STATION_TOTALCNT,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,b.TOTAL_COUNT,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计ETC入口工班表数据(条):" + count);

            //2.统计MTC出口工班表数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_ENSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "  select x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE,sum(nvl(x.TOTAL_COUNT,0)) TOTAL_COUNT\n" +
                    "  from TB_SYNC_ENSHIFT x,(\n" +
                    "    select STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO,max(SEQ_ID) SEQ_ID \n" +
                    "    from TB_SYNC_ENSHIFT group by STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,SQUARD_TYPE,OPERATORNO) y \n" +
                    "  where x.SQUARD_TYPE='mtc' and x.STATION_ID=y.STATION_ID and x.SQUAD_DATE=y.SQUAD_DATE and x.SQUAD_NO=y.SQUAD_NO and x.LANE_NO=y.LANE_NO \n" +
                    "  and x.SQUARD_TYPE=y.SQUARD_TYPE and nvl(x.OPERATORNO,-51)=nvl(y.OPERATORNO,-51) and x.SEQ_ID=y.SEQ_ID \n" +
                    "  and x.SQUAD_DATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') \n" +
                    "  group by x.STATION_ID,x.SQUAD_DATE,x.SQUAD_NO,x.LANE_NO,x.SQUARD_TYPE \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET a.STATION_TOTALCNT=nvl(a.STATION_TOTALCNT,0)+b.TOTAL_COUNT,UPDATE_TIME=sysdate \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,STATION_TOTALCNT,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,b.TOTAL_COUNT,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计MTC入口工班表数据(条):" + count);

            //3.统计出口工班表数据, 获取6天前的数据
            sql = "MERGE INTO TB_STATIS_STATION_ENSHIFT a \n" +
                    "USING ( \n" +
                    "  select tmp.*,s.ROAD_ID from ( \n" +
                    "    SELECT StationNo STATION_ID,SquadDate SQUAD_DATE,SquadNo SQUAD_NO,LaneNo LANE_NO,COUNT(1) TOTAL_COUNT \n" +
                    "    from TB_SYNC_TRC_ENPU \n" +
                    "    where SQUADDATE>to_char(sysdate-interval'"+m_statisDays+"'day,'yyyymmdd') and INSERTTIME>sysdate-interval'"+nBeforeDays+"'day \n" +
                    "    GROUP BY StationNo,SquadDate,SquadNo,LaneNo \n" +
                    "  ) tmp left join tb_charge_station s on tmp.STATION_ID=s.STATION_ID \n" +
                    ") b \n" +
                    "ON (a.STATION_ID=b.STATION_ID and a.SQUAD_DATE=b.SQUAD_DATE and a.SQUAD_NO=b.SQUAD_NO and a.LANE_NO=b.LANE_NO) \n" +
                    "WHEN MATCHED THEN \n" +
                    "  UPDATE SET a.TOTALCNT=b.TOTAL_COUNT,UPDATE_TIME=sysdate \n" +
                    "WHEN NOT MATCHED THEN \n" +
                    "  INSERT (SEQ_ID,ROAD_ID,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,TOTALCNT,RECEIVE_TIME) \n" +
                    "  VALUES (SEQ_STATIS_EXSHIFT1_ID.nextval,b.ROAD_ID,b.STATION_ID,b.SQUAD_DATE,b.SQUAD_NO,b.LANE_NO,b.TOTAL_COUNT,sysdate) ";
            count = DBUtils.execute(connPre, sql);
            XxlJobLogger.log("[普通]统计省中心接收入口流水数据(条):" + count);

            connPre.commit();
            XxlJobLogger.log("任务执行完成");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    XxlJobLogger.log("出现运行异常："+e);
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                    throw new Exception("回滚失败");
                }
            }
            throw e;//将异常抛给容器处理
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                    throw new Exception("关闭数据库连接失败");
                }
            }
        }
    }
}
